package edu.nctu.csie.jichang.database.model.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import edu.nctu.csie.jichang.database.dbinfo.LoginInfo;
import edu.nctu.csie.jichang.database.util.DBException;

public class DBConnPostgreSQL extends AbstractDBConnection{
	
	private static final Logger LOG = LoggerFactory.getLogger(DBConnPostgreSQL.class);
	
	protected DBConnPostgreSQL(LoginInfo pLoginUserInfo) {
		super(pLoginUserInfo);
		SCHEMA_PATTERN = "public";
		UNIQUE_COLUMN_TYPE = 3;
	}

	static{
		try{
			Class.forName("org.postgresql.Driver");
		}catch (Exception e) {
			throw new DBException(e);
		}
	}
	
	public void createConnection() {
		try{
			if(conn == null || conn.isClosed()){
				String tDBName   = loginInfo.getSchema().trim();
				String tDBHost   = loginInfo.getDatabaseInfo().getHost().trim();
				String tDBPort   = loginInfo.getDatabaseInfo().getPort().trim();
				String tUserName = loginInfo.getDatabaseInfo().getName().trim();
				String tUserPwd  = loginInfo.getDatabaseInfo().getPassword().trim();
				conn = DriverManager.getConnection("jdbc:postgresql://"+tDBHost+":"+tDBPort+"/"+tDBName,tUserName,tUserPwd);
			}
		}catch (Exception e) {
			throw new DBException(loginInfo.toString(), e);
		}
	}

	public void dropDatabase() {
		Connection tConn  = null;
		Statement  tState = null;
		try {
			String tDBName = loginInfo.getSchema().trim();
			tConn = createManagerConnection();
			tState = tConn.createStatement();
			String tSQL = "DROP DATABASE IF EXISTS "+tDBName;
			tState.executeUpdate(tSQL);
			LOG.info("Drop database " + tDBName);
		}catch (Exception e) {
			throw new DBException(e);
		}finally{
			close(tState);
			close(tConn);
		}
	}

	public void dropAndCreateDatabase() {
		Connection tConn  = null;
		Statement  tState = null;
		try {
			String tDBName = loginInfo.getSchema().trim();
			tConn  = createManagerConnection();
			tState = tConn.createStatement();
			String tSQL = "DROP DATABASE IF EXISTS "+tDBName;
			tState.executeUpdate(tSQL);
			tSQL = "CREATE DATABASE "+tDBName+ " WITH OWNER=postgres ENCODING='UTF8' TABLESPACE=pg_default CONNECTION LIMIT = -1;";
			tState.executeUpdate(tSQL);
			LOG.info("CREATE database " + tDBName);
		}catch (Exception e) {
			throw new DBException(e);
		}finally{
			close(tState);
			close(tConn);
		}
	}
	
	private Connection createManagerConnection() throws Exception {
		String tDBHost   = loginInfo.getDatabaseInfo().getHost().trim();
		String tDBPort   = loginInfo.getDatabaseInfo().getPort().trim();
		String tUserName = loginInfo.getDatabaseInfo().getName().trim();
		String tUserPwd  = loginInfo.getDatabaseInfo().getPassword().trim();
		return DriverManager.getConnection("jdbc:postgresql://"+tDBHost+":"+tDBPort+"/",tUserName,tUserPwd);
	}

}